﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OfficeOpenXml;
using AWA.VAT.DbAccess.SQL;
using System.Diagnostics;

namespace AWA.VAT.DbAccess.Excel
{
    public class ExcelToSQLReader : ExcelBase
    {
        public Dictionary<string, Editor> Editors { get; private set; } // email, Editor
        public Dictionary<string, Attendance> Attendances { get; private set; } // email, Attendance
        public Dictionary<string, Artist> Artists { get; private set; } // name, Artist
        public Dictionary<string, Song> Songs { get; private set; } // name, song
        public Dictionary<string, Source> Sources { get; private set; } // name, source
        public List<Entry> Entries { get; private set; }
        public Dictionary<string, Award> Awards { get; private set; }
        //public List<AMVs_Locations> Locations { get; private set; }

        private SQLLookup _lookup;

        public ExcelToSQLReader(string fileLocation, SQLLookup lookup)
        {
            this.FileLocation = fileLocation;
            this._lookup = lookup;

            Editors = new Dictionary<string, Editor>();
            Attendances = new Dictionary<string, Attendance>();
            Artists = new Dictionary<string, Artist>();
            Songs = new Dictionary<string, Song>();
            Sources = new Dictionary<string, Source>();
            Entries = new List<Entry>();
            Awards = new Dictionary<string, Award>();
        }

        public void ReadFile()
        {
            ExcelPackage excel = new ExcelPackage(new System.IO.FileInfo(this.FileLocation));
            ExcelWorksheet ws = excel.Workbook.Worksheets[_tabName];

            AWA_VATEntities entities = new AWA_VATEntities();

            // nine of 215 entries use multiple songs; ignore for now
            for (int i = _firstRow; i <= _lastRow; i++)
            {
                ReadEditorAttendance(ws, entities, i);
                ReadSongArtist(ws, i); // consider moving in to ReadEntry
                ReadEntry(ws, entities, i);
            }
            entities.Dispose();
            excel.Dispose();
        }

        private void ReadEditorAttendance(ExcelWorksheet ws, AWA_VATEntities entities, int i)
        {
            string email = ws.Cells[EmailCell + i.ToString()].Text;

            if (Editors.ContainsKey(email))
                return;

            string realName = ws.Cells[RealNameCell + i.ToString()].Text;
            string creatorName = ws.Cells[CreatorNameCell + i.ToString()].Text;
            string studio = ws.Cells[StudioCell + i.ToString()].Text;
            string birthText = ws.Cells[BirthdateCell + i.ToString()].Text;
            DateTime? birthdate = null;
            try
            {
                if (!String.IsNullOrEmpty(birthText))
                    birthdate = DateTime.Parse(birthText);
            }
            catch (Exception)
            {
                // ignore format error
                Debug.WriteLine(String.Format("Failed to parse birthdate ({0}) for {1}", birthText, creatorName));
            }
            string street = ws.Cells[StreetAddressCell + i.ToString()].Text;
            string city = ws.Cells[CityCell + i.ToString()].Text;
            string regionText = ws.Cells[StateCell + i.ToString()].Text;
            string zip = ws.Cells[ZIPCell + i.ToString()].Text;
            string countryText = ws.Cells[CountryCell + i.ToString()].Text;

            Region region;
            try { region = entities.Regions.Single(r => r.RegionAbbrev == regionText || r.RegionName == regionText); }
            catch (Exception)
            {
                Debug.WriteLine(String.Format("Failed to match Region ({0}) for {1}", regionText, creatorName)); // TODO: implement logging
                return;
            }

            Country country;
            try { country = entities.Countries.Single(r => r.EnglishName == countryText || r.LocalName == countryText); }
            catch (Exception)
            {
                Debug.WriteLine(String.Format("Failed to match Country ({0}) for {1}", countryText, creatorName)); // TODO: implement logging
                return;
            }

            Editor editor = new Editor()
            {
                RealName = realName,
                CreatorName = creatorName,
                Studio = studio,
                Email = email,
                Birthdate = birthdate,
                StreetAddress = street,
                City = city,
                RegionID = region.RegionID,
                PostalCode = zip,
                CountryID = country.CountryID
            };

            Editors.Add(email, editor);

            // get attendance while I'm at it
            string sentString = ws.Cells[EmailSentCell + i.ToString()].Text;
            bool sent = String.IsNullOrEmpty(sentString) ? false : sentString.ToLower()[0] == 'y';
            string respondedString = ws.Cells[RespondedCell + i.ToString()].Text;
            bool responded = String.IsNullOrEmpty(respondedString) ? false : respondedString.ToLower()[0] == 'y';
            string attendingString = ws.Cells[AttendingCell + i.ToString()].Text;
            bool attending = String.IsNullOrEmpty(attendingString) ? false : attendingString.ToLower()[0] == 'y';
            string remarks = ws.Cells[RespondRemarkCell + i.ToString()].Text;
            string checkedInString = ws.Cells[CheckedInCell + i.ToString()].Text;
            bool checkedIn = String.IsNullOrEmpty(checkedInString) ? false : checkedInString.ToLower()[0] == 'y';

            Attendance attendance = new Attendance()
            {
                Editor = editor,
                EmailSent = sent,
                Responded = responded,
                Attending = attending,
                Remarks = remarks,
                CheckedIn = checkedIn,
                AtShowing = checkedIn
            };

            Attendances.Add(email, attendance);
        }

        private void ReadEntry(ExcelWorksheet ws, AWA_VATEntities entities, int i)
        {
            string contestText = ws.Cells[ContestCell + i.ToString()].Text;
            Contest contest = entities.Contests.Single(c => c.ContestName == contestText);
            string title = ws.Cells[AMVTitleCell + i.ToString()].Text;
            Artist artist = Artists[ws.Cells[ArtistCell + i.ToString()].Text]; // nine have multiple
            Song song = Songs[ws.Cells[SongCell + i.ToString()].Text]; // nine have multiple

            List<Source> sources = new List<Source>();
            Source source1 = GetSource(ws, i, Source1Cell);
            if (source1 != null)
                sources.Add(source1);
            Source source2 = GetSource(ws, i, Source2Cell);
            if (source2 != null)
                sources.Add(source2);
            Source source3 = GetSource(ws, i, Source3Cell);
            if (source3 != null)
                sources.Add(source3);

            List<Award> awards = ReadAwards(ws, i);

            Editor editor = Editors[ws.Cells[EmailCell + i.ToString()].Text];

            AMV amv = new AMV()
            {
                AMVTitle = title,
                Songs = new List<Song>(new Song[] { song } ),
                Sources = sources.Count > 0 ? sources : null,
                Editor = editor
            };

            List<string> locations = ReadLocations(ws, i);
            List<AMVs_Locations> amvLocations = new List<AMVs_Locations>();
            foreach (string location in locations)
            {
                amvLocations.Add(new AMVs_Locations()
                                 {
                                     AMV = amv,
                                     Location = location,
                                     IsPublic = location.Contains("animemusicvideos.org") //vimeo? youtube? amv.de? ??
                                 });
            }
            amv.AMVs_Locations = amvLocations.Count > 0 ? amvLocations : null;

            Entry entry = new Entry()
            {
                AMV = amv,
                Contest = contest,
                Awards = awards.Count > 0 ? awards : null
            };

            Entries.Add(entry);
        }

        private List<string> ReadLocations(ExcelWorksheet ws, int i)
        {
            List<string> locations = new List<string>();

            // check Comments column
            string comments = ws.Cells[CommentsCell + i.ToString()].Text;
            if (comments.Contains("http"))
                locations.Add(comments.Substring(comments.IndexOf("http")));

            // check Link column
            string link = ws.Cells[LinkCell + i.ToString()].Text;
            if (link.Contains("http"))
                locations.Add(link.Substring(comments.IndexOf("http")));

            return locations;
        }

        private List<Award> ReadAwards(ExcelWorksheet ws, int i)
        {
            List<Award> awards = new List<Award>();
            string award1 = ws.Cells[Award1Cell + i.ToString()].Text;
            if (!String.IsNullOrEmpty(award1))
                awards.Add(GetAward(award1));
            string award2 = ws.Cells[Award2Cell + i.ToString()].Text;
            if (!String.IsNullOrEmpty(award2))
                awards.Add(GetAward(award2));

            return awards;
        }

        private void ReadSongArtist(ExcelWorksheet ws, int i)
        {
            string artistName = ws.Cells[ArtistCell + i.ToString()].Text;
            string songName = ws.Cells[SongCell + i.ToString()].Text;

            if (Songs.ContainsKey(songName) && Songs[songName].Artist.ArtistName == artistName)
                return;

            Artist artist;
            if (Artists.ContainsKey(artistName))
                artist = Artists[artistName];
            else
            {
                artist = new Artist() { ArtistName = artistName };
                Artists.Add(artistName, artist);
            }

            Song song = new Song()
            {
                SongName = songName,
                Artist = artist
            };

            Songs.Add(songName, song);
        }

        private Source GetSource(ExcelWorksheet ws, int row, string sourceColumn)
        {
            string sourceName = ws.Cells[sourceColumn + row.ToString()].Text;

            if (String.IsNullOrEmpty(sourceName) || sourceName.ToLower() == "none" || sourceName.ToLower() == "various")
                return null;

            if (!Sources.ContainsKey(sourceName))
                Sources.Add(sourceName, new Source() { SourceName = sourceName });

            return Sources[sourceName];
        }

        private Award GetAward(string award)
        {
            if (!Awards.ContainsKey(award))
                Awards.Add(award, new Award() { AwardName = award });

            return Awards[award];
        }
    }
}
